package chwgigs_jdbc;



// jcbc connection 객체를 생성하는 메서드 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class J03_MemberDAO implements J03_IMemberDAO {

   private final String DRV = "oracle.jdbc.driver.OracleDriver";
   private final String URL = "jdbc:oracle:thin:chwgigs/chwgigs@112.216.253.125:1521:xe";
   private String insertSQL = "insert into j03Member values (exam.nextval, ?, ?, ?, ?, sysdate)";
   private String selectSQL = "select * from J03Member order by mno desc" ;
   private String updateSQL = "update J03MEMBER set passwd = ?, name = ?, email = ?, regdate=sysdate  where mno=?";
   private String deleteSQL = "delete J03MEMBER  where mno = ?";   // where절을 안쓰면 싹 다 지워지기 때문에 반드시 where절을 쓴다.                                                                                                                               //
   
   @Override
   public boolean insertMember(J03_MemberVO m) {
      Connection conn = null;
      PreparedStatement pstmt = null;
      boolean result = false;
      
      try {
         conn = this.openDatabase();
         pstmt = conn.prepareStatement(insertSQL);
         //? 매개변수에 대입할 값 지정 
         pstmt.setString(1,m.getUserid() );
         pstmt.setString(2,m.getPasswd());
         pstmt.setString(3,m.getName());
         pstmt.setString(4,m.getEmail());
         
         pstmt.executeUpdate();
         result = true;
         
      } catch (Exception ex) {
         ex.printStackTrace();
      } finally {
         this.closeDatabase(conn, pstmt, null);
      }
      
      return result;
    // insertMember
   }
   // JDBC Connection 객체를 생성하는 메서드
   private Connection openDatabase() {
      try {
         Class.forName(DRV);
         return DriverManager.getConnection(URL);
      } catch (Exception e) {
         e.printStackTrace();
      }
      return null;
   }
   
   // JDBC객체를 닫는 메서드
   private void closeDatabase(Connection c, PreparedStatement p, ResultSet r) {
      if (r != null) try{r.close(); r = null;} catch(Exception e){}
      if (p != null) try{p.close(); p = null;} catch(Exception e){}
      if (c != null) try{c.close(); c = null;} catch(Exception e){}
   }
    //jdbc connection 

   
   // select 문으로 전체 회원정보를 조회한 후 
   // MemberVO 를 내용으로 갖는 동적배열 LIST 로 결과를 넘김 

 @Override
public List<J03_MemberVO> selectMember() {
	 Connection conn=null;
     PreparedStatement pstmt =null;
     ResultSet rs= null;
	 List<J03_MemberVO >result=new ArrayList <>();
     
	 try {conn = this.openDatabase();
     pstmt = conn.prepareStatement(selectSQL);
     rs=pstmt.executeQuery();
    
     while (rs.next()){
    	 // 이 부분만 바뀌고 나머지는 같다.
    	 J03_MemberVO m =new J03_MemberVO();
    	 m.setMno(rs.getString("mno"));
    	 m.setUserid(rs.getString("userid"));
    	 m.setPasswd(rs.getString("passwd"));
    	 m.setName(rs.getString("name"));
    	 m.setEmail(rs.getString("email"));
    	 m.setRegdate(rs.getString("regdate"));
    	 result.add(m);
     }
     
     }catch (Exception ex){
    	 ex.printStackTrace();
     }finally{
    	 closeDatabase(conn,pstmt,rs);
     }
     
     return result;
}

@Override
public boolean updateMember(J03_MemberVO m) {
         Connection conn =null;
         PreparedStatement pstmt = null;
	     boolean result = false;
      try {
    	  conn=openDatabase();
    	  pstmt = conn.prepareStatement(updateSQL);
    	    pstmt.setString(1,m.getPasswd());
    	    pstmt.setString(2,m.getName());
    	    pstmt.setString(3,m.getEmail());
    	    pstmt.setString(4,m.getMno());
    	
    	 int cnt = pstmt.executeUpdate();
    	 if (cnt==0) System.out.println("명령을 잘 실행 됬지만 수정된 데이터는 없음");
    	 result=true;
      }catch(Exception ex){
    	  ex.printStackTrace();
      }finally{
    	  this.closeDatabase(conn, pstmt, null);
      }
	  
      return result;
        
   }

@Override
public boolean deleteMember(J03_MemberVO m) {
	   Connection conn = null;
	   PreparedStatement pstmt = null;
	   boolean result = false;
	 
	   try {
		    conn=openDatabase();
		    pstmt = conn.prepareStatement(deleteSQL);

		    pstmt.setString(1,m.getMno());
		
		    int cnt = pstmt.executeUpdate();
	    	 if (cnt==0) System.out.println("명령을 잘 실행 됬지만 수정된 데이터는 없음");
		    result=true;
	   }catch (Exception ex){
		   ex.printStackTrace();
	   }finally {
	         this.closeDatabase(conn, pstmt, null); 
	      }
        	
	   return result;






}//


   

}//